.\" SPDX-License-Identifier: CC-BY-SA-4.0 or-later
.\" SPDX-FileCopyrightText: 2021-2022 grommunio GmbH
.TH gromox\-kdb2mt 8 "" "Gromox" "Gromox admin reference"
.SH Name
\fBgromox\-kdb2mt\fP \(em Utility for analysis/importing of Zarafa/Kopano
SQL-stored mailboxes
.SH Synopsis
\fBgromox\-kdb2mt\fP [\fB\-pstv\fP] [\fB\-\-sql\-host\fP \fIhostname\fP]
[\fB\-\-sql\-user\fP \fIidentity\fP] [\fB\-\-sql\-port\fP \fInumber\fP]
[\fB\-\-sql\-db\fP \fIname\fP] \fB\-\-src\-attach\fP \fIdir\fP
{\fB\-\-mbox\-guid\fP \fImboxguid\fP|\fB\-\-mbox\-name\fP
\fIusername\fP|\fB\-\-mbox\-mro\fP \fIusername\fP} [...]
.SH Description
gromox\-kdb2mt reads one store from a Zarafa/Kopano SQL database and, for
attachments, the associated filesystem. The data is then re-exported in a
Gromox-specific mailbox transfer format to stdout, intended for consumption by
pipe by the gromox-mt2exm(8gx) program. Optionally, kdb2mt can print a summary
of the hierarchy during extraction.
.PP
The SQL server that carries the Zarafa/Kopano database for the home server
of the user must be active. Databases with a schema version \fBn61\fP or
newer (ZCP versions 7.0.3 and onwards, and all KC versions) are supported.
The kopano\-server(8) process need not be running. Its LDAP need not be
available either.
.PP
ACLs can be extracted, but, owing to the independence of the Kopano LDAP
data model, require pre- or post-processing (see below).
.PP
The directory of Kopano attachments of the home server should be made available
in the mount namespace wherever gromox\-kdb2mt runs.
.PP
Properties of the \fIstore object itself\fP are only shown (if and when \-p is
used), but never dumped to the MT data stream. Properties of the store \fIroot
folder object\fP are(!) transferred.
.SH Options
.TP
\fB\-p\fP
Show properties in detail (enhances \fB\-t\fP).
.TP
\fB\-s\fP
Map the source mailbox folder hierarchy (and its subobjects) to the target
mailbox's hierarchy and splice objects accordingly. Only use \-s when both the
source side and the target side are private stores. See the section "Splice
mode" below for more information. When \-\-with\-hidden is not selected, \-s
will imply \-\-without\-hidden by default to avoid polluting e.g. "QuickStep
settings" (which may already have settings).
.TP
\fB\-t\fP
Show a diagnostic tree view of the source data as it is being read.
.TP
\fB\-v\fP
Print message count progress while processing larger folders. This option has
no effect if (the even more verbose) \fB\-t\fP option was used.
.TP
\fB\-\-loglevel\fP \fIn\fP Maximum verbosity of general logging (not connected
to \fB\-p\fP, \fB\-t\fP or \fB\-v\fP). 1=crit, 2=error, 3=warn, 4=notice,
5=info, 6=debug.
.br
Default: \fI4\fP (notice)
.TP
\fB\-\-user\-map\fP \fIfile\fP
Use the given file to perform ACL mapping. See section "ACL Extraction" below
for details.
.TP
\fB\-\-sql\-host\fP \fIhostname\fP
Hostname for the source SQL connection.
.br
Default: (MySQL default; \fIlocalhost\fP)
.TP
\fB\-\-sql\-port\fP \fInumber\fP
Port for the source SQL connection.
.br
Default: (MySQL default; automatic)
.TP
\fB\-\-sql\-user\fP \fIidentity\fP
Username for the source SQL connection.
.br
Default: \fIroot\fP
.TP
\fB\-\-sql\-db\fP \fIdbname\fP
Database name.
.br
Default: \fIkopano\fP
.TP
\fB\-\-src\-attach\fP \fIdirectory\fP
Required specification to the /var/lib/kopano/attachments directory mounted
somewhere locally. (To skip over file-based attachments, use the empty
value, i.e. \fB\-\-src\-attach ""\fP.)
.TP
\fB\-\-mbox\-guid\fP \fIguid\fP
Selects the mailbox with the particular GUID for extraction.
(This may be used to read orphaned stores.)
.TP
\fB\-\-mbox\-user\fP \fIusername\fP
Scan the user map (cf. \-\-user\-map) for a mailbox which was used by the given
username, and use it for extraction.
(To get a listing of all stores, use \fB\-\-mbox\-user ""\fP.)
.TP
\fB\-\-mbox\-mro\fP \fIusername\fP
Scan the source database for a mailbox which appears to have last been used by
\fIusername\fP, and use it for extraction. There are a number of \fBcaveats\fP
related to this lookup; see the section "Store lookup by name" further below.
(To get a listing of all stores, use \fB\-\-mbox\-mro ""\fP.)
.TP
\fB\-\-l1\fP \fIx\fP, \fB\-\-l2\fP \fIy\fP
If you are using "attachment_storage=files_v1-\fIx\fP-\fIy\fP" in
kopano-server.cfg, call kdb2mt with the L1 and L2 options.
.TP
\fB\-\-only\-obj\fP \fIhid\fP
Extract just the object with the given hierarchy id. This option may be
specified multiple times to selectively extract more objects. In the output
stream, objects so extracted will be declared as unanchored so they can be
imported to a folder of choice later (cf. gromox-mt2exm \-B).
.TP
\fB\-\-acl\fP={\fIauto\fP|\fIno\fP|\fInoextract\fP|\fIextract\fP|\fIconvert\fP}
Handling for ACLs on MAPI objects: ignore, extract to synthetic address, or
convert to an e-mail address. \fIauto\fP plus the presence of \-\-user\-map
leads to \fIconvert\fP. \fIauto\fP plus the absence of \-\-user\-map leads to
\fInoextract\fP. See the section "ACL Extraction" below for some more details.
.TP
\fB\-\-with\-hidden\fP, \fB\-\-without\-hidden\fP
This option controls the import of folders that have PR_ATTR_HIDDEN=1.
.SH Splice mode
Normally, kdb2mt will have a folder mapping table that specifies one entry,
which is "(Source root)" to "(Target root)\\Top of Information Store\\Import of
GUID @date". Any objects within an entry's source folder (including more
folders) are copied. This default entry makes sure absolutely everything is
imported, without loss.
.PP
Using the \-s option, this behavior will change. The default mapping is
replaced by one that will intermix imported folders with an existing hierarchy.
Specifically, special folders such as the root folder item, Top of Information
Store (a.k.a. TOIS or IPM_SUBTREE), Inbox, etc. are mapped. This only works
when both the source and target are private stores!
.PP
Special folders are identified by metadata, not by name. This way, kdb2mt can
support localized folder names and correctly map, for example, a German
"Gesendete Elemente" to a French "Éléments envoyés" (Sent Items). Regular
folders will be processed normally (by name), e.g. "(Source root)\\Top of
Information Store\\Invoices" will be imported at "(Target root)\\Top of
Information Store\\Invoices".
.PP
The \-s option is most useful when importing one's own store from one system to
another that's new and blank. If importing someone \fIelse's\fP store into
yours, leaving out \-s is normally the desired behavior, since you may not want
want to mix your (existing) with their mails.
.SH Environment variables
.TP
\fBSQLPASS\fP
Password for the source SQL connection.
.SH Examples
.SS Common scenario (Separate hosts)
When Gromox and Kopano run on different hosts, and you wish to have the Gromox
host to initiate all necessary connections.
.PP
Step 1. Establish an sshfs mount. This is used to get at the attachments
directory of Kopano Core. Command:
.PP
.RS 4
sshfs root@kp:/var/lib/kopano/attachments /mnt
.RE
.PP
For this to work, root logins need to be possible in some form (password or
pubkey-based authentication).
.PP
Step 2. Establish an SSH tunnel. This is used to get at the MariaDB/MySQL
database, assuming that this database is not already accepting connections on
port 3306. Command:
.PP
.RS 4
ssh \-L 12345:localhost:3306 root@kp
.RE
.PP
This way, the database can be accessed as 127.0.0.1:12345 later.
.PP
Step 3. Locate the MariaDB connection parameters that you want to use. You can
use the MariaDB "root" user (if available), or reuse the credentials from
/etc/kopano/server.cfg (often a "kopano" user).
.PP
Step 4. Run the conversion. The use of "127.0.0.1" is necessary to bypass the
special meaning of "localhost" (which implies the use of an AF_LOCAL socket,
e.g. /run/mysql/mysql.sock). Command:
.PP
.RS 4
SQLPASS=kopanosqlpass gromox\-kdb2mt \-\-sql\-host 127.0.0.1 \-\-sql\-port
12345 \-\-sql\-user kopano \-\-src\-attach /mnt \-\-mbox\-mro jdoe |
gromox\-mt2exm \-u user@domain.example
.RE
.PP
Done! The speed of the operation depends on the capabilities of the network
and the source database (latency more so than throughput).
.SS Other options
If the Gromox host is not allowed to connect to the Kopano host for reasons
of networking and/or firewall setups, there are plenty of other ways to
carry over the data. Administrators are asked to use their experience to
mix and match the plethora of utilities available at their disposal.
Possible operations include mysqldump(1), sftp(1), rsync(1), tar(1) and
curl(1).
.SH Store lookup using Kopano tools
If kdb2mt's built-in heuristic \-\-mbox\-mro resolution mechanism is not
adequate enough, you can use utilities from the Kopano installation, provided
that is still active.
.IP \(bu 4
`kopano\-storeadm \-M` is the gold standard. This dumps the entire store list,
in JSON representation. The GUIDs can then be used together with
\-\-mbox\-guid.
.IP \(bu 4
The global "SYSTEM" user object in Kopano also happens to have a private store,
titled "Inbox \- SYSTEM". This store however is practically empty and it is
unlikely it will ever need extraction. Alternatively, its GUID can also be
shown with `kopano\-admin \-\-details SYSTEM`.
.IP \(bu 4
The global public store in Kopano, if it exists, is owned by the "Everyone"
\fIgroup object\fP. In kopano\-storeadm output, it can be found by looking for
the display name "Public Folders". There is no way to see the GUID via
kopano\-admin.
.IP \(bu 4
Just for completeness: There is no per-company SYSTEM user (and hence no
store). If anything, companies re-use the global SYSTEM user as a member.
.IP \(bu 4
The per-company public folder, if it exists, is owned by the respective
\fIcompany object\fP. In kopano\-storeadm output, it can be found by looking
for the display name "Public Folders - MyCompany". Alternatively, the GUID can
also be shown with `kopano\-admin \-\-type company \-\-details MyCompany`.
.SH Store lookup by name
Generally, Kopano SQL databases do not store usernames. Store ownership is
recorded with a Kopano-level numeric ID, which itself is mapped to a
site-specific attribute of an authentication service, e.g. the uidNumber field
of an LDAP. Only the authentication service would know the username, and kdb2mt
does not rely on the existence of such authentication provider.
.PP
Every store has a metadata field for the \fBmost recent owner\fP (MRO). This
field was intended for orphaned stores and has informational value only. The
MRO field is not always updated by Kopano services, which can lead to
\-\-mbox\-mro not necessarily finding an expected match. In particular,
kopano\-server misses doing the MRO update on store detach, and on changes to
the username in LDAP.
.PP
Furthermore, because it is possible to detach/orphan and create a new store for
a user (and repeatedly so), the MRO field value is \fBnot unique\fP across the
set of all stores.
.PP
Furthermore, the MRO field is missing the domain/company part of the username.
Company public stores (in hosted setups) use the company name as MRO. This all
contributes to \-\-mbox\-mro possibly matching multiple stores.
.PP
When more than one store matches in any way, kdb2mt will print the result set
with GUIDs and exit, at which point you need to use \-\-mbox\-guid instead.
.SH ACL Extraction
Because kdb2mt works completely LDAP-less, it knows nothing about users save
for their numeric user object ID on the homeserver and a reference to an LDAP
object (the so-called "Extern id", e.g. objectUUID/uidNumber). The user object
ID is local to a kopano-server instance. The composition of the object ID and
server instance GUID forms a unique token. ACEs are carried over such that that
permissions for user with a given \fIobjid\fP are transformed to the synthetic
identity \fIobjid\fP@\fIserverguid\fP.kopano.invalid.
.PP
.RS 4
.nf
sqlite3 /var/lib/gromox/user/1/1/exmdb/exchange.sqlite3
.
sqlite> select * from permissions;
member_id  folder_id  username                                             permission
---------  ---------  ---------------------------------------------------  ----------
1          15         default                                              2048
2          24         default                                              2048
3          2090545    256@aa8e2b20b2054ca98987ea1053c3bb16.kopano.invalid  1177
.fi
.RE
.PP
kdb2mt can be instructed to map these to a new email address using the
\-\-user\-map command-line option. That file can be generated by executing
kdb\-uidextract(8) or the kdb\-uidextract\-limited(8) helper programs on the
original, live Kopano system. (With some effort, the file can also be manually
constructed.)
.SH ZARAFA Address Type
MAPI as a system supports referencing a message's participants by arbitrary
target descriptors. These include, but are not limited to, SMTP e-mail
addresses, usernames, LDAP RDNs, X.400 address, or even a fax number.
For example, the "EX" address type employed by Exchange uses the ESSDN
(Enterprise/Site/Server Distinguished Name) of the user, which is sort of a
LDAP Relative Distinguished Name (RDN).
.PP
Zarafa/Kopano systems define a "ZARAFA" address type, and the identifiers
contain the username (possibly in other forms such as company\\username or
company@username) or the (SMTP) email address; it is not entirely consistent.
With the help of a user map file, the ZARAFA-type addresses can be converted to
SMTP addresses.
.SH See also
\fBgromox\fP(7), \fBgromox\-mt2exm\fP(8)
